﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Text;

namespace MyIIS
{
    public class DBO
    {
        private static string myDB = string.Format(ConfigurationManager.ConnectionStrings["DB_ConnectionString"].ConnectionString, ConfigurationManager.AppSettings["ServerID"], ConfigurationManager.AppSettings["UserID"], ConfigurationManager.AppSettings["Password"], ConfigurationManager.AppSettings["DataBase"]);

        /// <summary>
        /// 获取所有站点配置列表
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable getAll()
        {
            string sql = "select [GUID],[SiteName],[TelDirectory],[UniDirectory],[Enable],(case [Enable] when 1 then '启用' else '禁用' end) status from [QT_SiteList] order by [SiteName] asc";
            DataTable dt = new QT.DBO.SqlDBHelper(myDB).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取启用状态的站点配置列表
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable getUsing()
        {
            string sql = "select [GUID],[SiteName],[TelDirectory],[UniDirectory] from [QT_SiteList] where [enable]=1 order by [SiteName] asc";
            DataTable dt = new QT.DBO.SqlDBHelper(myDB).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取站点按IP访问统计结果
        /// </summary>
        /// <param name="startdate">开始日期</param>
        /// <param name="enddate">结束日期</param>
        /// <param name="sitename">站点ID</param>
        /// <param name="stype">站点类型：1为电信，0为联通</param>
        /// <param name="num">数量。0为全部记录</param>
        /// <returns>DataTable</returns>
        public static DataTable getAnalybyIP(string date, string sitename, string stype, int num)
        {
            string sql = @"select" + (num == 0 ? "" : (" top " + num)) + " [clientip],sum([ipcount]) as mycount,[iparea],[ipaddress] from [QT_AnalybyIP] where [sitename]='" + sitename + "' and [stype]=" + stype + " and [date]='" + date + "' group by [clientip],[iparea],[ipaddress] order by mycount desc";
            DataTable dt = new QT.DBO.SqlDBHelper(myDB).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取站点按页面访问统计结果
        /// </summary>
        /// <param name="startdate">开始日期</param>
        /// <param name="enddate">结束日期</param>
        /// <param name="sitename">站点ID</param>
        /// <param name="stype">站点类型：1为电信，0为联通</param>
        /// <param name="num">数量。0为全部记录</param>
        /// <returns>DataTable</returns>
        public static DataTable getAnalybyPage(string date, string sitename, string stype, int num)
        {
            string sql = @"select" + (num == 0 ? "" : (" top " + num)) + " a.[url] as 页面,sum([pagecount]) as 访问次数,b.[comment] as 备注 from [QT_AnalybyPage] a left join [QT_PageComment] b on a.[sitename]=b.[sitename] and a.[url]=b.[uri] where a.[sitename]='" + sitename + "' and a.[stype]=" + stype + " and a.[date]='" + date + "' group by a.[url],b.[comment] order by 访问次数 desc";
            DataTable dt = new QT.DBO.SqlDBHelper(myDB).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取站点日志概要统计信息
        /// </summary>
        /// <param name="startdate">开始日期</param>
        /// <param name="enddate">结束日期</param>
        /// <param name="sitename">站点ID</param>
        /// <returns></returns>
        public static DataTable getAnalybyGeneral(string startdate, string enddate, string sitename)
        {
            string sql = "select convert(varchar(12) , [date], 23 ) as 日期,[pvcounttel] as 电信PV量,[pvcountuni] as 联通PV量,[pagecounttel] as 电信独立页面,[pagecountuni] as 联通独立页面,[ipcounttel] as 电信独立访问IP,[ipcountuni] as 联通独立访问IP from [QT_AnalybyGeneral] where [sitename]='" + sitename + "' and [date]>='" + startdate + "' and [date]<='" + enddate + "' order by [date] desc";
            DataTable dt = new QT.DBO.SqlDBHelper(myDB).DataTable(sql);
            return dt;
        }
    }

    public class QQWryHelper
    {
        #region Attributes

        private static FileStream ipFile;
        private static long ip;
        private static string ipfilePath;

        public string file;
        public string separator;

        private static long StartIndex = 0;
        private static long EndIndex = 0;
        private static long Count = 0;

        #endregion

        #region 构造函数

        public QQWryHelper()
        {
        }

        ///<summary> 
        /// 构造函数 
        ///</summary> 
        ///<param name="ipfilePath">纯真IP数据库路径</param>
        public QQWryHelper(string ipFilePath)
        {
            ipfilePath = ipFilePath;
        }

        #endregion

        #region CheckOut

        ///<summary> 
        /// 获取指定IP所在地理位置 
        ///</summary> 
        ///<param name="strIP">要查询的IP地址</param> 
        ///<returns></returns> 
        public IPLocation GetIPLocation(string strIP)
        {
            ip = IPToLong(strIP);
            ipFile = new FileStream(ipfilePath, FileMode.Open, FileAccess.Read);
            long[] ipArray = BlockToArray(ReadIPBlock());
            long offset = SearchIP(ipArray, 0, ipArray.Length - 1) * 7 + 4;
            ipFile.Position += offset;//跳过起始IP 
            ipFile.Position = ReadLongX(3) + 4;//跳过结束IP 

            IPLocation loc = new IPLocation();
            loc.ip = strIP;
            int flag = ipFile.ReadByte();//读取标志 
            if (flag == 1)//表示国家和地区被转向 
            {
                ipFile.Position = ReadLongX(3);
                flag = ipFile.ReadByte();//再读标志 
            }
            long countryOffset = ipFile.Position;
            loc.country = ReadString(flag);

            if (flag == 2)
            {
                ipFile.Position = countryOffset + 3;
            }
            flag = ipFile.ReadByte();
            loc.area = ReadString(flag);

            ipFile.Close();
            ipFile = null;
            return loc;
        }

        #endregion

        #region Method

        #region IP

        ///<summary> 
        /// 将字符串形式的IP转换为long 
        ///</summary> 
        ///<param name="strIP"></param> 
        ///<returns></returns> 
        private static long IPToLong(string ip_Long)
        {
            char[] separator = new char[] { '.' };
            if (ip_Long.Split(separator).Length == 3)
            {
                ip_Long = ip_Long + ".0";
            }
            string[] strArray = ip_Long.Split(separator);
            long num2 = ((long.Parse(strArray[0]) * 0x100L) * 0x100L) * 0x100L;
            long num3 = (long.Parse(strArray[1]) * 0x100L) * 0x100L;
            long num4 = long.Parse(strArray[2]) * 0x100L;
            long num5 = long.Parse(strArray[3]);
            return (((num2 + num3) + num4) + num5);
        }

        /// <summary>
        /// 将long形式的IP转换为字符串
        /// </summary>
        /// <param name="ip_Int"></param>
        /// <returns></returns>
        private static string LongToIP(long ip_Int)
        {
            long num = (long)((ip_Int & 0xff000000L) >> 0x18);
            if (num < 0L)
            {
                num += 0x100L;
            }
            long num2 = (ip_Int & 0xff0000L) >> 0x10;
            if (num2 < 0L)
            {
                num2 += 0x100L;
            }
            long num3 = (ip_Int & 0xff00L) >> 8;
            if (num3 < 0L)
            {
                num3 += 0x100L;
            }
            long num4 = ip_Int & 0xffL;
            if (num4 < 0L)
            {
                num4 += 0x100L;
            }
            return (num.ToString() + "." + num2.ToString() + "." + num3.ToString() + "." + num4.ToString());
        }

        private static string LongToString(long ip_Int)
        {
            long num = (long)((ip_Int & 0xff000000L) >> 0x18);
            if (num < 0L)
            {
                num += 0x100L;
            }
            long num2 = (ip_Int & 0xff0000L) >> 0x10;
            if (num2 < 0L)
            {
                num2 += 0x100L;
            }
            long num3 = (ip_Int & 0xff00L) >> 8;
            if (num3 < 0L)
            {
                num3 += 0x100L;
            }
            long num4 = ip_Int & 0xffL;
            if (num4 < 0L)
            {
                num4 += 0x100L;
            }
            return (num.ToString().PadLeft(3, '0') + num2.ToString().PadLeft(3, '0') + num3.ToString().PadLeft(3, '0') + num4.ToString().PadLeft(3, '0'));
        }

        #endregion

        #region Index

        ///<summary> 
        /// 将索引区字节块中的起始IP转换成Long数组 
        ///</summary> 
        ///<param name="ipBlock"></param> 
        private long[] BlockToArray(byte[] ipBlock)
        {
            long[] ipArray = new long[ipBlock.Length / 7];
            int ipIndex = 0;
            byte[] temp = new byte[8];
            for (int i = 0; i < ipBlock.Length; i += 7)
            {
                Array.Copy(ipBlock, i, temp, 0, 4);
                ipArray[ipIndex] = BitConverter.ToInt64(temp, 0);
                ipIndex++;
            }
            return ipArray;
        }

        ///<summary> 
        /// 从IP数组中搜索指定IP并返回其索引 
        ///</summary> 
        ///<param name="ipArray">IP数组</param> 
        ///<param name="start">指定搜索的起始位置</param> 
        ///<param name="end">指定搜索的结束位置</param> 
        ///<returns></returns> 
        private int SearchIP(long[] ipArray, int start, int end)
        {
            int middle = (start + end) / 2;
            if (middle == start) return middle;
            else if (ip < ipArray[middle]) return SearchIP(ipArray, start, middle);
            else return SearchIP(ipArray, middle, end);
        }

        ///<summary> 
        /// 读取IP文件中索引区块 
        ///</summary> 
        ///<returns></returns> 
        private byte[] ReadIPBlock()
        {
            StartIndex = ReadLongX(4);
            EndIndex = ReadLongX(4);
            Count = (EndIndex - StartIndex) / 7 + 1;//总记录数 
            ipFile.Position = StartIndex;
            byte[] ipBlock = new byte[Count * 7];
            ipFile.Read(ipBlock, 0, ipBlock.Length);
            ipFile.Position = StartIndex;
            return ipBlock;
        }

        #endregion

        ///<summary> 
        /// 从IP文件中读取指定字节并转换位long 
        ///</summary> 
        ///<param name="bytesCount">需要转换的字节数，主意不要超过8字节</param> 
        ///<returns></returns> 
        private long ReadLongX(int bytesCount)
        {
            byte[] _bytes = new byte[8];
            ipFile.Read(_bytes, 0, bytesCount);
            return BitConverter.ToInt64(_bytes, 0);
        }

        ///<summary> 
        /// 从IP文件中读取字符串 
        ///</summary> 
        ///<param name="flag">转向标志</param> 
        ///<returns></returns> 
        private string ReadString(int flag)
        {
            if (flag == 1 || flag == 2)//转向标志 
                ipFile.Position = ReadLongX(3);
            else
                ipFile.Position -= 1;

            List<byte> list = new List<byte>();
            byte b = (byte)ipFile.ReadByte();
            while (b > 0)
            {
                list.Add(b);
                b = (byte)ipFile.ReadByte();
            }
            return Encoding.Default.GetString(list.ToArray());
        }

        #endregion
    }

    #region Struct

    public struct IPLocation
    {
        public string ip;
        public string country;
        public string area;
    }

    public struct IPSection
    {
        public string StartIP;
        public string EndIP;
        public string Country;
        public string Area;
    }

    #endregion
}
